import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;
import util.HttpClientUtil;

import java.io.FileOutputStream;
import java.io.PrintStream;
import java.util.*;

public class Main104Application {
    private static final String url = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/";
    private static final Map<String, String> headers = new HashMap<>();

    // 一个浏览器一直请求也被拦下来，那就4个浏览器随机访问
    private static final String[] EXPLORER_LIST = {
        "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36", // 360极速
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36 Edg/97.0.1072.55",  // edge
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36",  // google
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:92.0) Gecko/20100101 Firefox/92.0" // firefox
    };
    private static final Random R = new Random();
    /**
     * 这里获取 村委会据维护的只能单独再写一个了
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {

        // String outputFile = "C:\\Users\\Administrator\\Desktop\\full.sql";
        // System.setOut(new PrintStream(new FileOutputStream(outputFile)));

        // 获取池对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 配置连接信息
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3308/my-info?serverTimezone=Asia/Shanghai");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        //设置初始的连接数
        dataSource.setInitialPoolSize(10);
        //获取连接
        // Connection connection = dataSource.getConnection();

        // Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/my-info?serverTimezone=Asia/Shanghai", "root", "123456");

        QueryRunner queryRunner = new QueryRunner(dataSource);
        /**
         *
         * "SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '104'"
         * 如果断号了，就顺着最后获取的记录 筛选parentId就可以了
         * "SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '104' AND `PARENT_ID` > '最后写入的`PARENT_ID`'"
         */
        List<Map<String, Object>> res = queryRunner.query("SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '104' AND `ID` > '654002103207'", new MapListHandler());
        // final List<Map<String, String>> villageList = new LinkedList<>();

        System.setOut(new PrintStream(new FileOutputStream("C:\\Users\\Administrator\\Desktop\\full.sql")));
        res.forEach(map -> {
            if (null == map.get("LINK")) return;
            // System.out.println(map);
//            try {
//                Thread.sleep(3000);
//            } catch (InterruptedException e) {
//                e.printStackTrace();
//            }
            getVillageList(map);
        });


    }

    public static void getVillageList(Map<String, Object> county) {

        /**
         * 应对反爬的处理办法
         */
        headers.put("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9");
        headers.put("Accept-Encoding", "gzip, deflate");
        headers.put("Accept-Language", "zh-CN,zh;q=0.9");
        headers.put("Connection", "keep-alive");
        // 这个cookie我是写死的， 用浏览器放一下抓一个CV下来就OK
        headers.put("Cookie", "SF_cookie_1=72991020; wzws_cid=0a3437ee54d480f4e9d7a803ce40ea209062fb89e676041702640cf8b68a8e6f100515f77a916d43eccbf9bf4ca4373ef6c83e6cddf6596b0c4c99b50dc6442d6370481133143c2de50c8bf386084682");
        // 声明代理主机
        headers.put("Host", "www.stats.gov.cn");
        String link = county.get("LINK").toString();
        // 放盗链地址要写上去
        headers.put("Referer",  link);
        headers.put("Upgrade-Insecure-Requests",  "1");
        // 最后这个是模仿浏览器访问
        headers.put("User-Agent", EXPLORER_LIST[R.nextInt(4)]);

        String htmlSourceCode = HttpClientUtil.executeGetRequest(link, headers);
        // System.out.println(htmlSourceCode);
        Document document = Jsoup.parse(htmlSourceCode);
        Elements villagetr = document.getElementsByClass("villagetr");

        villagetr.forEach(element -> {
            Elements tdTags = element.getElementsByTag("td");
            String villageCode = tdTags.get(0).text();
            String villageType = tdTags.get(1).text();
            String villageName = tdTags.get(2).text();
            // INSERT INTO `my-info`.`admin-region` (`ID`, `NAME`, `REGION_TYPE`, `PARENT_ID`, `LICENSE_SHORT`, `LINK`, `GEN_TIME`) VALUES
            System.out.printf("(%s, '%s', '%s', '%s', NULL, '%s', NOW()),\n", villageCode, villageName, villageType, county.get("ID"), link);
        });
    }
}
